const express = require('express');
const router = express.Router();
const db = require('../db');
const { authenticateToken, checkRole, getCurrentUser } = require('../middleware/authMiddleware');
const moment = require('moment-timezone');
// 在文件顶部添加
process.env.TZ = 'Asia/Shanghai';

// 设置为上海时区
const shanghaiTime = moment().tz('Asia/Shanghai');
console.log(shanghaiTime.format('YYYY-MM-DD HH:mm:ss Z')); // 输出：2023-10-01 12:34:56 +08:00
// 创建通知 (管理员和清算经理)
router.post('/', authenticateToken, checkRole('admin', 'payment_manager'), (req, res) => {
  const { recipientId, title, content, isRecurring, recurrenceType, recurrenceInterval, startDateTime } = req.body;
  const senderId = req.user.id;

  if (!recipientId || !title || !content) {
    return res.status(400).json({ message: '接收者ID、标题和内容是必填项' });
  }

  // 处理定时任务参数
  let recurrenceTypeValue = null;
  let recurrenceIntervalValue = null;
  let startDateTimeValue = null;
  let nextRunTimeValue = null;

  if (isRecurring) {
    if (!['month', 'week', 'day'].includes(recurrenceType)) {
      return res.status(400).json({ message: '无效的定时类型' });
    }
    if (!recurrenceInterval || isNaN(recurrenceInterval) || parseInt(recurrenceInterval) < 1) {
      return res.status(400).json({ message: '间隔必须是正整数' });
    }
    if (!startDateTime) {
      return res.status(400).json({ message: '开始时间不能为空' });
    }
    console.log(startDateTime);
    const startDate = moment.tz(startDateTime, 'Asia/Shanghai').toDate();
    if (isNaN(startDate.getTime())) {
      return res.status(400).json({ message: '无效的开始时间格式' });
    }
    if (startDate <= new Date()) {
      return res.status(400).json({ message: '开始时间必须大于当前时间' });
    }

    recurrenceTypeValue = recurrenceType;
    recurrenceIntervalValue = parseInt(recurrenceInterval);
    startDateTimeValue = startDateTime;
    nextRunTimeValue = startDateTime; // 初始下次运行时间设为开始时间
  }

  // 验证接收者是否为员工
  db.get(`SELECT u.id FROM users u
          JOIN roles r ON u.role_id = r.id
          WHERE u.id = ? AND r.name = 'employee'`, [recipientId], (err, employee) => {
    if (err) {
      return res.status(500).json({ message: '数据库错误', error: err.message });
    }

    if (!employee) {
      return res.status(400).json({ message: '接收者必须是员工角色' });
    }

    // 创建通知
    const sql = `INSERT INTO notifications (
                  title, content, sender_id, recipient_id,
                  recurrence_type, recurrence_interval, start_datetime, next_run_time
                 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`;
    db.run(sql, [
      title, content, senderId, recipientId,
      recurrenceTypeValue, recurrenceIntervalValue, startDateTimeValue, nextRunTimeValue 
    ], function(err) {
      if (err) {
        return res.status(500).json({ message: '创建任务失败', error: err.message });
      }
      console.log(moment.tz('Asia/Shanghai'));
      res.status(201).json({
        message: '通知发送成功',
        notificationId: this.lastID,
        title,
        content,
        recipientId,
        createdAt: shanghaiTime.format('YYYY-MM-DD HH:mm:ss Z')
      });
    });
  });
});

// 获取当前用户的通知
router.get('/', authenticateToken, (req, res) => {
  const userId = req.user.id;
  const { isRead, page = 1, limit = 10 } = req.query;
  const offset = (page - 1) * limit;

  let sql = `SELECT n.id, n.title, n.recurrence_type, n.content, n.is_read, n.created_at,
               sender.id as sender_id, sender.username as sender_username, sender.full_name as sender_full_name,
               recipient.username as recipient_username, recipient.full_name as recipient_full_name
            FROM notifications n
            JOIN users sender ON n.sender_id = sender.id
            JOIN users recipient ON n.recipient_id = recipient.id`;
    const params = [];

    // 根据角色添加WHERE条件
    if (req.user.role === 'admin') {
      sql += ` WHERE 1=1`; // 管理员查询所有通知
    } else if (req.user.role === 'payment_manager') {
      sql += ` WHERE n.recipient_id = ? OR n.sender_id = ?`;
      params.push(userId, userId); // 添加用户ID作为收件人和发件人条件
    } else {
      sql += ` WHERE n.recipient_id = ?`;
      params.push(userId); // 普通员工只能查看自己收到的通知
    }

    // 添加已读/未读筛选
    if (isRead !== undefined) {
      sql += ` AND n.is_read = ?`;
      params.push(isRead === 'true' || isRead === '1');
    }

  // 添加分页和排序
  sql += ` ORDER BY n.created_at DESC LIMIT ? OFFSET ?`;
  params.push(parseInt(limit), parseInt(offset));

  db.all(sql, params, (err, notifications) => {
    if (err) {
      return res.status(500).json({ message: '获取通知失败', error: err.message });
    }

    // 获取总数量用于分页
    let countSql = `SELECT COUNT(*) as total FROM notifications WHERE recipient_id = ?`;
    const countParams = [userId];

    if (isRead !== undefined) {
      countSql += ` AND is_read = ?`;
      countParams.push(isRead === 'true' || isRead === '1');
    }

    db.get(countSql, countParams, (err, countResult) => {
      if (err) {
        return res.status(500).json({ message: '获取通知数量失败', error: err.message });
      }

      res.json({
        notifications,
        pagination: {
          total: countResult.total,
          page: parseInt(page),
          limit: parseInt(limit),
          pages: Math.ceil(countResult.total / limit)
        }
      });
    });
  });
});

// 获取未读通知数量
router.get('/unread/count', authenticateToken, (req, res) => {
  const userId = req.user.id;

  const sql = `SELECT COUNT(*) as unread_count
               FROM notifications
               WHERE recipient_id = ? AND is_read = false`;

  db.get(sql, [userId], (err, result) => {
    if (err) {
      return res.status(500).json({ message: '获取未读通知数量失败', error: err.message });
    }

    res.json({ unreadCount: result.unread_count });
  });
});

// 获取管理员通知统计
router.get('/admin/stats', authenticateToken, checkRole('admin'), (req, res) => {
  // 总通知数
  const totalSql = `SELECT COUNT(*) as total FROM notifications`;
  // 总未读通知数
  const unreadSql = `SELECT COUNT(*) as unread FROM notifications WHERE is_read = false`;

  db.get(totalSql, [], (err, totalResult) => {
    if (err) {
      return res.status(500).json({ message: '获取总通知数失败', error: err.message });
    }

    db.get(unreadSql, [], (err, unreadResult) => {
      if (err) {
        return res.status(500).json({ message: '获取未读通知数失败', error: err.message });
      }

      res.json({
        totalNotifications: totalResult.total,
        unreadNotifications: unreadResult.unread
      });
    });
  });
});

// 获取发送的通知统计
router.get('/sent/stats', authenticateToken, checkRole('admin', 'payment_manager'), (req, res) => {
  const userId = req.user.id;
  // 发送的总通知数
  const totalSql = `SELECT COUNT(*) as total FROM notifications WHERE sender_id = ?`;
  // 发送的未读通知数
  const unreadSql = `SELECT COUNT(*) as unread FROM notifications WHERE sender_id = ? AND is_read = false`;

  db.get(totalSql, [userId], (err, totalResult) => {
    if (err) {
      return res.status(500).json({ message: '获取发送通知数失败', error: err.message });
    }

    db.get(unreadSql, [userId], (err, unreadResult) => {
      if (err) {
        return res.status(500).json({ message: '获取发送未读通知数失败', error: err.message });
      }

      res.json({
        totalSent: totalResult.total,
        unreadSent: unreadResult.unread
      });
    });
  });
});

// 获取接收的通知统计
router.get('/received/stats', authenticateToken, (req, res) => {
  const userId = req.user.id;
  // 接收的总通知数
  const totalSql = `SELECT COUNT(*) as total FROM notifications WHERE recipient_id = ?`;
  // 接收的未读通知数
  const unreadSql = `SELECT COUNT(*) as unread FROM notifications WHERE recipient_id = ? AND is_read = false`;

  db.get(totalSql, [userId], (err, totalResult) => {
    if (err) {
      return res.status(500).json({ message: '获取接收通知数失败', error: err.message });
    }

    db.get(unreadSql, [userId], (err, unreadResult) => {
      if (err) {
        return res.status(500).json({ message: '获取接收未读通知数失败', error: err.message });
      }

      res.json({
        totalReceived: totalResult.total,
        unreadReceived: unreadResult.unread
      });
    });
  });
});

// 标记通知为已读
router.patch('/:id/read', authenticateToken, (req, res) => {
  const { id } = req.params;
  const userId = req.user.id;

  // 验证通知是否存在且属于当前用户
  db.get('SELECT * FROM notifications WHERE id = ? AND recipient_id = ?', [id, userId], (err, notification) => {
    if (err) {
      return res.status(500).json({ message: '数据库错误', error: err.message });
    }

    if (!notification) {
      return res.status(404).json({ message: '通知不存在或不属于您' });
    }

    // 标记为已读
    db.run('UPDATE notifications SET is_read = true WHERE id = ?', [id], function(err) {
      if (err) {
        return res.status(500).json({ message: '更新通知状态失败', error: err.message });
      }

      res.json({ message: '通知已标记为已读', notificationId: id });
    });
  });
});

// 删除通知 (仅管理员或发送者)
router.delete('/:id', authenticateToken, (req, res) => {
  const { id } = req.params;
  const userId = req.user.id;
  const userRole = req.user.role;

  // 验证权限：管理员或发送者
  db.get('SELECT sender_id, recipient_id FROM notifications WHERE id = ?', [id], (err, notification) => {
    if (err) {
      return res.status(500).json({ message: '数据库错误', error: err.message });
    }

    if (!notification) {
      return res.status(404).json({ message: '通知不存在' });
    }

    if (userRole !== 'admin' && notification.sender_id !== userId) {
      return res.status(403).json({ message: '没有删除权限' });
    }

    // 删除通知
    db.run('DELETE FROM notifications WHERE id = ?', [id], function(err) {
      if (err) {
        return res.status(500).json({ message: '删除通知失败', error: err.message });
      }

      res.json({ message: '通知删除成功', notificationId: id });
    });
  });
});

module.exports = router;